SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TXMVTO_CNTB_Q25]    
/*------------------------------------------------------------*/    
/*----- Empresa          : OFISIS S.A.                    ----*/    
/*----- Cliente          : OFISIS S.A.                    ----*/    
/*----- Programa         : Compras PDT                    ----*/    
/*-----                    Para Terceros                  ----*/    
/*----- Nombre           : SP_TXMVTO_CNTB_Q25             ----*/    
/*----- Desarrollado por : Martin Garcia Farfan           ----*/    
/*----- Fecha            : 20/03/2001                     ----*/    
/*----- Base de Datos    : SQL Server                     ----*/    
/*----- Version          : 7.0                            ----*/    
/*------------------------------------------------------------*/    
/*------------------------------------------------------------*/    
/*----- Drop Proc SP_TXMVTO_CNTB_Q25                      ----*/    
/*----- Grant all on SP_TXMVTO_CNTB_Q25 to Public         ----*/    
/*-----   
SP_TXMVTO_CNTB_Q25 '01', 1, 2006, 3400, 2  
      ----*/    
/*------------------------------------------------------------*/    
@ISCO_EMPR       TD_VC_002,    
@INNU_CNTB_EMPR  TD_IN_001_CONT,    
@INNU_ANNO       TD_IN_004_ANNO,    
@INIM_UITS       TD_NU_016_004,    
@INNU_MAXI       TD_IN_001    
As    
    
Declare    
 @CSCO_PROV      TD_VC_020,    
 @CSNO_CORT_PROV TD_VC_100,    
 @CSNU_RUCS      TD_VC_020,    
 @CSTI_DOCU_SUNA TD_VC_003,    
 @CNIM_INAF_SOLE TD_NU_016_004,    
 @CNIM_AFEC_SOLE TD_NU_016_004,    
 @VNIM_TOTA      TD_NU_016_004,    
 @VNIM_TOPE      TD_NU_016_004,    
 @VNNU_CONT      TD_IN_001,    
 @VSNU_RUCS      TD_VC_020,    
 @VSNU_CONT      TD_VC_010,    
 @VSCO_PROV      TD_VC_020,    
 @VSNU_RUCS_TEMP TD_VC_020,    
 @VSNO_PROV_TEMP TD_VC_100,    
 @VSIM_TOTA      TD_VC_020,    
 @VSNU_ANNO      Varchar(04),    
 @VSDE_LINE      Varchar(255),    
    
 @VSCO_DOCU_SUNA   TD_VC_003,      
 @VSNU_DOCU_SUNA   TD_VC_020,      
 @VSAP_PATE_AUXI   TD_VC_020,    
 @VSAP_MATE_AUXI   TD_VC_020,    
 @VSNO_NATU_AUX1   TD_VC_020,    
 @VSNO_NATU_AUX2   TD_VC_020,    
 @VSNO_RAZO_SOCI   Varchar(200),    
    
 @CSCO_DOCU_SUNA   TD_VC_003,      
 @CSNU_DOCU_SUNA   TD_VC_020,      
 @CSAP_PATE_AUXI   TD_VC_020,    
 @CSAP_MATE_AUXI   TD_VC_020,    
 @CSNO_NATU_AUX1   TD_VC_020,    
 @CSNO_NATU_AUX2   TD_VC_020,    
 @CSNO_RAZO_SOCI   Varchar(200),    
 @VSTI_PERS_SUNA   Varchar(3),    
 @CSTI_PERS_SUNA   Varchar(3)    
    
 Delete TWPDTS_TERC    
    
 Select  @VSNU_RUCS = NU_RUCS    
 From TMEMPR    
 Where CO_EMPR = @ISCO_EMPR    
    
-- SE VALIDO QUE NO CARGUE LOS DOC DE PROV NO DOMICILIADOS    
 Declare CU_TXMVTO_CNTB_Q25_1 Cursor For    
 Select t1.CO_PROV, Max(t1.NO_CORT_PROV), Max(t1.NU_RUCS_PROV), t1.TI_DOCU_SUNA,     
 Isnull(Sum(Isnull(t1.IM_INAF_SOLE, 0)), 0),Isnull(Sum(Isnull(t1.IM_AFEC_SOLE, 0)), 0),    
 Max(t1.CO_DOCU_SUNA),Max(t1.NU_DOCU_SUNA),Max(t1.AP_PATE_AUXI),Max(t1.AP_MATE_AUXI),    
 Max(t1.NO_NATU_AUX1),Max(t1.NO_NATU_AUX2),Max(t1.NO_RAZO_SOCI),Max(t1.TI_PERS_SUNA)    
 From TWMVTO_Q024 t1,TMAUXI_EMPR t2    
 Where t1.TI_DOCU_SUNA != '02'    
 And t1.NO_CORT_PROV Is not NULL    
 And t2.CO_EMPR = @ISCO_EMPR    
 And t2.TI_AUXI_EMPR = T1.TI_AUXI_EMPR    
 And t2.CO_AUXI_EMPR = t1.CO_PROV    
 And IsNull(t2.TI_PERS_SUNA,'') != '03'    
 Group By t1.CO_PROV, t1.TI_DOCU_SUNA    
 Order By t1.CO_PROV, t1.TI_DOCU_SUNA    
    
 Open CU_TXMVTO_CNTB_Q25_1    
 Fetch CU_TXMVTO_CNTB_Q25_1 into @CSCO_PROV, @CSNO_CORT_PROV, @CSNU_RUCS,     
 @CSTI_DOCU_SUNA, @CNIM_INAF_SOLE, @CNIM_AFEC_SOLE,     
 @CSCO_DOCU_SUNA,@CSNU_DOCU_SUNA,@CSAP_PATE_AUXI,@CSAP_MATE_AUXI,    
 @CSNO_NATU_AUX1,@CSNO_NATU_AUX2,@CSNO_RAZO_SOCI,@CSTI_PERS_SUNA    
    
 Select @VSCO_PROV = @CSCO_PROV    
 Select @VNNU_CONT = 0    
 Select @VNIM_TOTA = 0    
 Select @VSDE_LINE = NULL    
 Select @VSNU_CONT = NULL    
 Select @VSIM_TOTA = NULL    
 Select @VSNU_ANNO = NULL    
     
 While @@Fetch_Status = 0    
 Begin    
   If @VSCO_PROV != @CSCO_PROV    
    Begin    
     Select @VNIM_TOPE = @INIM_UITS * @INNU_MAXI    
 --- Si el monto total es mayor a tantas UITS se mostrara en el archivo    
     If @VNIM_TOTA > @VNIM_TOPE    
      Begin    
       Select @VNNU_CONT = @VNNU_CONT + 1    
       Select @VSNU_CONT = Convert(Varchar(10), @VNNU_CONT)    
       Select @VSIM_TOTA = Convert(Varchar(20), Floor(@VNIM_TOTA))    
       Select @VSNU_ANNO = Convert(Varchar(04), @INNU_ANNO)    
/*       Select @VSDE_LINE = @VSNU_CONT + '|' + '6' + '|' + @VSNU_RUCS + '|' +     
              @VSNU_ANNO + '|' + '02' + '|' + '6' + '|' + @VSNU_RUCS_TEMP + '|' +    
              @VSIM_TOTA + '|' + '|'  + '|' + '|' + '|' + @VSNO_PROV_TEMP + '|' */    
        Select @VSDE_LINE = IsNull(Rtrim(Ltrim(@VSNU_CONT)),'') + '|' + '6' + '|' +     
              IsNull(Rtrim(Ltrim(@VSNU_RUCS)),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSNU_ANNO)),'') + '|' +     
              IsNull(Rtrim(Ltrim(Substring(@VSTI_PERS_SUNA,1,2))),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSCO_DOCU_SUNA)),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSNU_DOCU_SUNA)),'') + '|' +    
              IsNull(Rtrim(Ltrim(@VSIM_TOTA)),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSAP_PATE_AUXI)),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSAP_MATE_AUXI)),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSNO_NATU_AUX1)),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSNO_NATU_AUX2)),'') +'|' +     
              IsNull(Rtrim(Ltrim(@VSNO_RAZO_SOCI)),'') + '|'    
    
        If @VSDE_LINE IS NOT NULL    
          Insert TWPDTS_TERC(NU_CONT, CO_LINE)       
           values(@VNNU_CONT, Rtrim(Ltrim(@VSDE_LINE)))    
        Else    
          Select @VNNU_CONT = @VNNU_CONT - 1    
    
       --- Blanqueo Valores ---    
      End /*FIN If @VNIM_TOTA > @VNIM_TOPE*/    
    
      Select @VSCO_PROV = @CSCO_PROV    
      Select @VNIM_TOTA = 0.00    
      Select @VSDE_LINE = NULL    
      Select @VSNU_CONT = NULL    
      Select @VSIM_TOTA = NULL    
      Select @VSNU_ANNO = NULL    
    End /*FIN If @VSCO_PROV != @CSCO_PROV*/    
          
   if @CSTI_DOCU_SUNA != '07' and @CSTI_DOCU_SUNA != '08'    
    Select @VNIM_TOTA = @VNIM_TOTA + @CNIM_INAF_SOLE + @CNIM_AFEC_SOLE    
       
   if @CSTI_DOCU_SUNA = '08' -- NOTA DE DEBITO/NOTA DE CARGO BANCARIA    
    Select @VNIM_TOTA = @VNIM_TOTA + @CNIM_INAF_SOLE + @CNIM_AFEC_SOLE    
        
   if @CSTI_DOCU_SUNA = '07' -- NOTA DE CREDITO     
    Select @VNIM_TOTA = @VNIM_TOTA - Abs(@CNIM_INAF_SOLE) - Abs(@CNIM_AFEC_SOLE)    
       
   Select @VSNU_RUCS_TEMP = Rtrim(Ltrim(@CSNU_RUCS))    
   Select @VSNO_PROV_TEMP = Rtrim(Ltrim(@CSNO_CORT_PROV))     
   Select @VSCO_DOCU_SUNA = Rtrim(Ltrim(@CSCO_DOCU_SUNA))    
   Select @VSNU_DOCU_SUNA = Rtrim(Ltrim(@CSNU_DOCU_SUNA))    
   Select @VSAP_PATE_AUXI = Rtrim(Ltrim(@CSAP_PATE_AUXI))    
   Select @VSAP_MATE_AUXI = Rtrim(Ltrim(@CSAP_MATE_AUXI))    
   Select @VSNO_NATU_AUX1 = Rtrim(Ltrim(@CSNO_NATU_AUX1))    
   Select @VSNO_NATU_AUX2 = Rtrim(Ltrim(@CSNO_NATU_AUX2))    
   Select @VSNO_RAZO_SOCI = Rtrim(Ltrim(@CSNO_RAZO_SOCI))    
   Select @VSTI_PERS_SUNA = Rtrim(Ltrim(@CSTI_PERS_SUNA))    
    
  Fetch CU_TXMVTO_CNTB_Q25_1 into @CSCO_PROV, @CSNO_CORT_PROV, @CSNU_RUCS,     
  @CSTI_DOCU_SUNA, @CNIM_INAF_SOLE, @CNIM_AFEC_SOLE,     
  @CSCO_DOCU_SUNA,@CSNU_DOCU_SUNA,@CSAP_PATE_AUXI,@CSAP_MATE_AUXI,    
  @CSNO_NATU_AUX1,@CSNO_NATU_AUX2,@CSNO_RAZO_SOCI,@CSTI_PERS_SUNA    
 End    
    
Close CU_TXMVTO_CNTB_Q25_1    
Deallocate CU_TXMVTO_CNTB_Q25_1    
    
  Select @VNIM_TOPE = @INIM_UITS * @INNU_MAXI    
  --- Si el monto total es mayor a tantas UITS se mostrara en el archivo    
   If @VNIM_TOTA > @VNIM_TOPE    
    Begin    
     Select @VNNU_CONT = @VNNU_CONT + 1    
     Select @VSNU_CONT = Convert(Varchar(04), @VNNU_CONT)    
     Select @VSIM_TOTA = Convert(Varchar(20), Floor(@VNIM_TOTA))    
     Select @VSNU_ANNO = Convert(Varchar(04), @INNU_ANNO)    
/*     Select @VSDE_LINE = @VSNU_CONT + '|' + '6' + '|' + @VSNU_RUCS + '|' +     
            @VSNU_ANNO + '|' + '02' + '|' + '6' + '|' + @VSNU_RUCS_TEMP + '|' +    
            @VSIM_TOTA + '|' + '|'  + '|' + '|' + '|' + @VSNO_PROV_TEMP + '|'    
*/    
        Select @VSDE_LINE = IsNull(Rtrim(Ltrim(@VSNU_CONT)),'') + '|' + '6' + '|' +     
              IsNull(Rtrim(Ltrim(@VSNU_RUCS)),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSNU_ANNO)),'') + '|' +     
              IsNull(Rtrim(Ltrim(Substring(@VSTI_PERS_SUNA,1,2))),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSCO_DOCU_SUNA)),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSNU_DOCU_SUNA)),'') + '|' +    
              IsNull(Rtrim(Ltrim(@VSIM_TOTA)),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSAP_PATE_AUXI)),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSAP_MATE_AUXI)),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSNO_NATU_AUX1)),'') + '|' +     
              IsNull(Rtrim(Ltrim(@VSNO_NATU_AUX2)),'') +'|' +     
              IsNull(Rtrim(Ltrim(@VSNO_RAZO_SOCI)),'') + '|'    
    
      if @VSDE_LINE IS NOT NULL    
         Insert TWPDTS_TERC(NU_CONT, CO_LINE)       
           values(@VNNU_CONT, Rtrim(Ltrim(@VSDE_LINE)))    
    End /*FIN If @VNIM_TOTA > @VNIM_TOPE*/    
  
  
/*  
SELECT CO_LINE FROM TWPDTS_TERC  
ORDER BY NU_CONT*/  
  
    
Return    
/*------------------------------ Fin ---------------------------------*/    



GO
